﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DAO;
using System.Configuration;
using Entidades;

namespace DAO
{
    public class UsuarioDAO
    {
        //**********************insertar Usuario
        public static void Insertar(Usuario usu)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            string sql = "insert into Usuario (userName, dni, nombre, apellido, password, id_Rol, email, id_TipoDni) values (@user, @dni, @nom, @ape, @pass, @rol, @email, @tipoDni); select @@identity;";
            
            cmd.Parameters.AddWithValue("@user", usu.User);
            cmd.Parameters.AddWithValue("@dni", usu.NumDni);
            cmd.Parameters.AddWithValue("@nom", usu.Nombre);
            cmd.Parameters.AddWithValue("@ape", usu.Apellido);
            cmd.Parameters.AddWithValue("@pass", usu.Pass);
            cmd.Parameters.AddWithValue("@rol", usu.Id_Rol);
            cmd.Parameters.AddWithValue("@email", usu.Mail);
            cmd.Parameters.AddWithValue("@tipoDni", usu.Id_TipoDni);            
            try
            {
                cn.Open();                
                cmd.CommandText = sql;
                cmd.Connection = cn;
                cmd.CommandType = CommandType.Text;                
                cmd.ExecuteNonQuery();            
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al insertar el Usuario:" + ex.Message);
            }
            finally
            {
                cn.Close();
            }    
        }

        //******************buscar por id

        public static Usuario BuscarPorDni(int dni)
        {
            Usuario usu = null;
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd1 = new SqlCommand();
            cmd1.Connection = cn;
            try
            {
                cn.Open();
                cmd1.CommandText = "select userName, dni, nombre, apellido, password, id_Rol, email, id_TipoDni from Usuario where dni=@Dni";
                cmd1.Parameters.AddWithValue("@Dni", dni);
                SqlDataReader dr = cmd1.ExecuteReader();
                while (dr.Read())
                {
                    usu = new Usuario();
                    usu.User = dr["userName"].ToString();
                    usu.NumDni = int.Parse(dr["dni"].ToString());
                    usu.Nombre = dr["nombre"].ToString();
                    usu.Apellido = dr["apellido"].ToString();
                    usu.Pass = dr["password"].ToString();
                    usu.Id_Rol = int.Parse(dr["id_Rol"].ToString());
                    usu.Mail = dr["email"].ToString();
                    usu.Id_TipoDni = int.Parse(dr["id_TipoDni"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al buscar el Usuario:" + ex.Message);

            }
            finally
            {
                cn.Close();
            }       
            return usu;
        }

        public static Usuario BuscarPorUser(string user)
        {
            Usuario usu = null;
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd1 = new SqlCommand();
            cmd1.Connection = cn;
            try
            {
                cn.Open();
                cmd1.CommandText = "select userName, dni, nombre, apellido, password, id_Rol, email, id_TipoDni from Usuario where userName=@userName";
                cmd1.Parameters.AddWithValue("@userName", user);
                SqlDataReader dr = cmd1.ExecuteReader();
                while (dr.Read())
                {
                    usu = new Usuario();
                    usu.User = dr["userName"].ToString();
                    usu.NumDni = int.Parse(dr["dni"].ToString());
                    usu.Nombre = dr["nombre"].ToString();
                    usu.Apellido = dr["apellido"].ToString();
                    usu.Pass = dr["password"].ToString();
                    usu.Id_Rol = int.Parse(dr["id_Rol"].ToString());
                    usu.Mail = dr["email"].ToString();
                    usu.Id_TipoDni = int.Parse(dr["id_TipoDni"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al buscar el Usuario:" + ex.Message);

            }
            finally
            {
                cn.Close();
            }
            return usu;
        }
        //*********************dar baja
        public static void DarBaja(int usuarioDni)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            try
            {
                cn.Open();
                cmd.CommandText = "delete from Usuario where dni=@Dni";
                cmd.Parameters.Add(new SqlParameter("@Dni", usuarioDni));
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Erroar al eliminar el Usuario:" + ex.Message);

            }
            finally
            {
                cn.Close();

            }
        }

        //********************para listar/cargar los dni
        public static DataTable CargarDni()
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);            
            DataTable dt = new DataTable();

            try
            {
                cn.Open();
                SqlDataAdapter da = new SqlDataAdapter("Select id_TipoDni, nombre from TipoDni", cn);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al cargar los Dni:" + ex.Message);
            }
            finally
            {
                cn.Close();
            }
            return dt;
        }

        //********************para listar/cargar los roles
        public static DataTable CargarRol()
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);            
            DataTable dt = new DataTable();

            try
            {
                cn.Open();
                SqlDataAdapter da = new SqlDataAdapter("Select id_Rol, nombre from Rol", cn);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al cargar los Roles:" + ex.Message);
            }
            finally
            {
                cn.Close();
            }
            return dt;
        }

        public static DataTable cargarGrilla(string orden)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;            
            DataTable dt = new DataTable();
            try
            {
                cn.Open();
                cmd.CommandText = "select u.userName, u.dni, u.nombre, u.apellido, u.password, u.email, r.nombre from  Usuario u, Rol r where u.id_Rol=r.id_Rol";
                if (orden == "")
                    cmd.CommandText += " order by u.nombre";
                else
                {
                    if (orden.Equals("nombre1"))
                    {
                        cmd.CommandText += " order by r.nombre";
                    }
                    else
                    {
                        if (orden.Equals("nombre"))
                        {
                            cmd.CommandText += " order by u.nombre";
                        }
                        else
                            cmd.CommandText += " order by " + orden;
                    }

                }
                SqlDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al cargar la Grilla:" + ex.Message);
            }
            finally
            {
                cn.Close();
            }
            return dt;
        }

        public static DataTable cargarGrillaByFiltro(int? rol, string user, string apellido)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            DataTable dt = new DataTable();
            try
            {
                cn.Open();
                string sql = "select u.userName, u.dni, u.nombre, u.apellido, u.password, u.email, r.nombre from  Usuario u, Rol r where u.id_Rol=r.id_Rol";

                if (rol != null)
                {
                    sql += " and r.id_Rol=@Rol";
                    cmd.Parameters.AddWithValue("@Rol", rol);
                }
                if (!string.IsNullOrEmpty(user))
                {
                    sql += " and u.userName like @userName";
                    cmd.Parameters.AddWithValue("@userName", user + "%");
                }
                if (!string.IsNullOrEmpty(apellido))
                {
                    sql += " and u.apellido like @ape";
                    cmd.Parameters.AddWithValue("@ape", apellido + "%");
                }                
                
                cmd.CommandText = sql;
                SqlDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al cargar grilla por filtros:" + ex.Message);
            }
            finally
            {
                cn.Close();
            }
            return dt;
        }

        public static void updateUser(Usuario usu)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            try 
            {
                cn.Open();
                cmd.CommandText = "update Usuario set nombre=@nom, apellido=@ape, id_Rol=@rol, email=@mail, dni=@numDni,id_TipoDni=@tipoDni where userName=@User";
                cmd.Parameters.AddWithValue("@nom", usu.Nombre);
                cmd.Parameters.AddWithValue("@ape", usu.Apellido);
                cmd.Parameters.AddWithValue("@rol", usu.Id_Rol);
                cmd.Parameters.AddWithValue("@mail", usu.Mail);
                cmd.Parameters.AddWithValue("@tipoDni", usu.Id_TipoDni);
                cmd.Parameters.AddWithValue("@numDni", usu.NumDni);
                cmd.Parameters.AddWithValue("@User", usu.User);

                cmd.ExecuteNonQuery();                
            }
            catch (Exception e)
            {
                throw new ApplicationException("Error al actualizar el contacto. " + e.Message);
            }
            finally
            {
                cn.Close();
            }
        }

    }
    
}
